﻿using NPOI.HSSF.UserModel;
using System.Collections.Generic;
using System.IO;

namespace Ding.Offices.Excels.Npoi
{
    public class ExcelHelper
    {
        /// <summary>
        /// 合并多个Excel文件，只支持2003格式的
        /// </summary>
        /// <param name="bytes"></param>
        /// <returns></returns>
        public static byte[] MergeExcel(List<byte[]> bytes)
        {
            HSSFWorkbook product = new HSSFWorkbook();
            int itemIndex = 1;
            var list = new List<string>();  // 工作表名集合
            foreach (var byteArray in bytes)
            {
                if (byteArray == null || byteArray.Length == 0)
                {
                    continue;
                }
                using (MemoryStream stream = new MemoryStream())
                {
                    stream.Write(byteArray, 0, byteArray.Length);
                    HSSFWorkbook book1 = new HSSFWorkbook(stream);

                    for (int i = 0; i < book1.NumberOfSheets; i++)
                    {
                        HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
                        //avoid sheet has same name
                        string reportName = sheet1.SheetName;
                        if (list.Exists(x => x == reportName))
                        {
                            reportName = reportName + "_Sheet0" + itemIndex;
                        }
                        list.Add(reportName);
                        if (string.IsNullOrWhiteSpace(reportName))
                        {
                            reportName = "";
                        }
                        else
                        {
                            reportName = reportName.Replace("/", "").Replace("\\", "").Replace("[", "").Replace("]", "");
                        }
                        sheet1.CopyTo(product, reportName, true, true);
                        itemIndex++;
                    }
                }
            }
            using (var fs = new MemoryStream())
            {
                product.Write(fs);
                return fs.ToArray();
            }
        }
    }
}
